package com.hafu.dao;

import com.hafu.model.SPinfo;
import com.hafu.util.DbUtil;
import com.hafu.util.StringUtil;
import com.mysql.jdbc.PreparedStatement;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class SpInfoDao {
    //添加商品信息
    public Boolean addSpInfo(SPinfo sp){
        String sql="insert into Spinfo(name,price,sell_price) values(?,?,?)";
        Connection conn = DbUtil.getConn();
        try {
            PreparedStatement preparedStatement = (PreparedStatement) conn.prepareStatement(sql);
            preparedStatement.setString(1,sp.getName());
            preparedStatement.setBigDecimal(2,sp.getPrice());
            preparedStatement.setBigDecimal(3,sp.getSellPrice());
            //执行添加sql方法
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("添加成功");
                return true;
            } else return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;}
    //删除商品信息
    public Boolean deleteSpInfo (int id){
        String sql="delete from Spinfo where id=?";
        Connection conn = DbUtil.getConn();
        try {
            PreparedStatement preparedStatement = (PreparedStatement) conn.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            //执行添加sql方法
            int i=preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
                return true;
            } else return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace() ;
        }
        return false;
    }
    //更新商品信息
    public Boolean updateSpInfo(SPinfo sp){
        String sql="update Spinfo set name=?,price=?,sell_price=? where id=?";
        Connection conn = DbUtil.getConn();
        try {
            PreparedStatement preparedStatement= (PreparedStatement) conn.prepareStatement(sql);
            preparedStatement.setString(1,sp.getName());
            preparedStatement.setBigDecimal(2,sp.getPrice());
            preparedStatement.setBigDecimal(3,sp.getSellPrice());
            preparedStatement.setInt(4,sp.getId());
            int i=preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("修改成功");
                return true;
            } else return false;
        }catch (SQLException throwables){
            throwables.printStackTrace();
        }
        return false;
    }
    //根据商品名称模糊查询商品信息
    public ArrayList<SPinfo> getSpInfoByName (SPinfo sp) {
        String sql="select * from spinfo";
        if (!StringUtil.isEmpty(sp.getName())) {
            //模糊查询
            sql=sql+"where name like '%"+sp.getName()+"%'";
        }
        Connection conn = DbUtil.getConn();
        ArrayList<SPinfo> spList=new ArrayList<>();
        try {
            PreparedStatement preparedStatement= (PreparedStatement) conn.prepareStatement(sql);
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                BigDecimal price = resultSet.getBigDecimal("price");
                BigDecimal sellPrice = resultSet.getBigDecimal("sell_price");
                SPinfo spInfo = new SPinfo(id, name, price, sellPrice);
                spList.add(spInfo);
            }
        }catch (SQLException throwables){
            throwables.printStackTrace();
        }
        return spList;
    }
}
